﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;


namespace yule.Data
{
    /// <summary>
    /// 数据库访问类
    /// </summary>
    public abstract class DBHelper : IDBHelper
    {
        private static object _instance_lock = new object();
        private static DBHelper _instance = null;
        /// <summary>
        /// 实例对象
        /// </summary>
        public static DBHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_instance_lock)
                    {
                        if (_instance == null)
                        {
                            try
                            {
                                string connStr = yule.Util.AppSettings.GetAppSetting(yule.Const.AppSettings.ConnStr);
                                if (yule.Util.Verificate.IsNull(connStr) == false)
                                {
                                    _instance = new yule.Data.SQL2008.DBHelper(connStr);
                                }
                            }
                            catch (Exception ex)
                            {
                                _instance = null;
                                throw ex;
                            }
                        }
                    }
                }
                return _instance;
            }
        }

        #region ExecuteNonQuery

        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, Action<string> action = null)
        {
            int val = this.ExecuteNonQuery(cmdText, null, null);

            if (val > 0 && action != null)
            {
                action(cmdText);
            }

            return val;
        }

        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, IList<DbParameter> commandParameters, Action<string, IList<DbParameter>> action = null)
        {
            int val = this.ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
            if (val > 0 && action != null)
            {
                action(cmdText, commandParameters);
            }
            return val;
        }

        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, IList<DbParameter> commandParameters, Action<string, IList<DbParameter>> action = null)
        {
            DbCommand cmd = this.CreateDbCommand();
            System.Data.Common.DbConnection conn = this.CreateDbConnection(this.ConnectionString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                if (val > 0 && action != null)
                {
                    action(cmdText, commandParameters);
                }
                cmd.Parameters.Clear();
                return val;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        #endregion ExecuteNonQuery

        #region Transaction

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction trans, string cmdText)
        {
            return this.ExecuteNonQuery(trans, cmdText, null);
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction trans, string cmdText,  IList<DbParameter> commandParameters)
        {
            return this.ExecuteNonQuery(trans, CommandType.Text, cmdText, commandParameters);
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,  IList<DbParameter> commandParameters)
        {
            DbCommand cmd = this.CreateDbCommand();

            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            return val;
        }

        #endregion

        #region ExecuteDataTable


        /// <summary>
        /// 执行查询返回DataSet
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string cmdText)
        {
            return this.ExecuteDataTable(cmdText, null);
        }

        /// <summary>
        /// 执行查询返回DataSet
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string cmdText,  IList<DbParameter> commandParameters)
        {
            return this.ExecuteDataTable(CommandType.Text, cmdText, commandParameters);
        }

        /// <summary>
        /// 执行查询返回DataSet
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(CommandType cmdType, string cmdText,  IList<DbParameter> commandParameters)
        {
            DbCommand cmd = this.CreateDbCommand();
            System.Data.Common.DbConnection conn = this.CreateDbConnection(this.ConnectionString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                DbDataAdapter da = this.CreateDbDataAdapter(cmd);

                da.SelectCommand.CommandTimeout = 120;

                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }

            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 执行查询返回,第一行第一列
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText)
        {
            return this.ExecuteScalar(cmdText, null);
        }
        /// <summary>
        /// 执行查询返回,第一行第一列
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText,  IList<DbParameter> commandParameters)
        {
            return this.ExecuteScalar(CommandType.Text, cmdText, commandParameters);
        }
        /// <summary>
        /// 执行查询返回,第一行第一列
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText,  IList<DbParameter> commandParameters)
        {
            DbCommand cmd = this.CreateDbCommand();
            System.Data.Common.DbConnection conn = this.CreateDbConnection(this.ConnectionString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        #endregion

        /// <summary>
        /// 创建事务
        /// </summary>
        /// <returns></returns>
        public abstract Transaction CreateTransaction();
        /// <summary>
        /// 创建DbCommand
        /// </summary>
        /// <returns></returns>
        protected abstract DbCommand CreateDbCommand();
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        protected abstract DbConnection CreateDbConnection(string connectionString);
        /// <summary>
        /// 创建查询适配器
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        protected abstract DbDataAdapter CreateDbDataAdapter(DbCommand cmd);

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <param name="direction"></param>
        /// <returns></returns>
        public abstract DbParameter CreateDbParameter(string parameterName, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input);

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get;
            set;
        }

        /// <summary>
        /// 格式化Command
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        internal static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, IList<DbParameter> cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    if (parm.Value != null)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
            }
        }


    }
}